﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Windows.UI.Xaml;
using com.IronOne.BoardPACWinAppBO.Meeting;
using com.IronOne.BoardPACWinAppDAO.DbOperations;
using com.IronOne.BoardPACWinAppDAO.DB;
using com.IronOne.BoardPACWinAppUtil.Enums;
using com.IronOne.BoardPACWinAppUtil.Util;
using SQLite;

namespace com.IronOne.BoardPACWinAppDAO.Meeting
{
    public class MeetingDAO : IMeetingDAO
    {
        private readonly IDbOperations _dbOperations;

        public MeetingDAO()
        {
            _dbOperations = new DbOperations.DbOperations();
        }

        /// <summary>
        ///     categoryId == -1 and subCategoryId == -1 will return all meetings
        /// </summary>
        /// <param name="categoryId">Send -1 if not applicable</param>
        /// <param name="subCategoryId">Send -1 if not applicable</param>
        /// <returns></returns>
        public async Task<List<MeetingModel>> GetMeetings(int categoryId, int subCategoryId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                List<MeetingModel> meetings = null;

                if (categoryId == -1 && subCategoryId == -1) //All
                {
                    var mList = await conn.Table<DB.Meeting>().ToListAsync();
                    if (mList == null || mList.Count < 0) return null;

                    meetings = EntityToModelMapper.MapMeetingEntityToMeetingModel(mList);
                }

                if (categoryId == -1 && subCategoryId != -1)
                {
                    var mList =
                        await conn.Table<DB.Meeting>().Where(x => x.SubCategoryId == subCategoryId).ToListAsync();
                    if (mList == null || mList.Count < 0) return null;

                    meetings = EntityToModelMapper.MapMeetingEntityToMeetingModel(mList);
                }

                if (categoryId != -1 && subCategoryId == -1)
                {
                    var mList = await conn.Table<DB.Meeting>().Where(x => x.CategoryId == categoryId).ToListAsync();
                    if (mList == null || mList.Count < 0) return null;

                    meetings = EntityToModelMapper.MapMeetingEntityToMeetingModel(mList);
                }

                if (categoryId != -1 && subCategoryId != -1)
                {
                    var mList = await conn.Table<DB.Meeting>().Where(x => x.CategoryId == categoryId &&
                                                                          x.SubCategoryId == subCategoryId)
                        .ToListAsync();
                    if (mList == null || mList.Count < 0) return null;

                    meetings = EntityToModelMapper.MapMeetingEntityToMeetingModel(mList);
                }

                var lastMeetings =
                    meetings.Where(x => x.MeetingStatus == (byte) MeetingStatus.Last).OrderBy(y => y.Date).ToList();
                var nextMeetings =
                    meetings.Where(x => x.MeetingStatus == (byte) MeetingStatus.Next).OrderBy(y => y.Date).ToList();
                var scheduledMeetings =
                    meetings.Where(x => x.MeetingStatus == (byte) MeetingStatus.Schedule).OrderBy(y => y.Date).ToList();

                if (nextMeetings != null)
                    lastMeetings.AddRange(nextMeetings);
                if (scheduledMeetings != null)
                    lastMeetings.AddRange(scheduledMeetings);

                //get from log
                var pending =
                    (await
                        conn.Table<Log>()
                            .Where(x => x.Action.Equals(ApplicationConstants.LogActions.UpdateViewedPaper))
                            .ToListAsync()).Select(y => y.PaperID);

                var enumerable = pending as IList<int?> ?? pending.ToList();

                foreach (var item in lastMeetings)
                {
                    item.CommentsCount =
                        await
                            conn.ExecuteScalarAsync<int>(
                                "SELECT COUNT(Comments.meetingId) FROM Comments WHERE Comments.meetingId=" +
                                item.MeetingId +
                                " AND Comments.type == 1 AND Comments.status == 0 AND comments.memberId != " +
                                Global.UserId + " GROUP BY Comments.meetingId");
                    item.CommentIconShowHide = item.CommentsCount > 0 ? Visibility.Visible : Visibility.Collapsed;

                    item.PaperNotificationStatus = 10;

                    var newPapers =
                        await
                            conn.ExecuteScalarAsync<int>(
                                "SELECT COUNT(PaperId) FROM Paper INNER JOIN Heading ON Paper.HeadingID = Heading.HeadingID INNER JOIN Meeting ON Heading.MeetingId = Meeting.MeetingId WHERE DocType != 3 AND NotificationStatus IN (0) AND RefId = " +
                                item.MeetingId +
                                " AND MeetingStatus IN (1,4,5) AND PaperStatus NOT IN (0,4,6) AND Heading.IsAllowed = 1 AND Paper.IsAllowed = 1");

                    
                    if (pending != null && enumerable.Contains(newPapers))
                        //If pending in log table show nothing
                    {
                        item.PaperNotificationStatus = (int) PapernotificationStatus.Read;
                        continue;
                    }

                    if (newPapers > 0)
                    {
                        item.PaperNotificationStatus = (int) PapernotificationStatus.New;
                        continue;
                    }

                    if (newPapers > 0) continue;

                    var ammendedPapers =
                        await
                            conn.ExecuteScalarAsync<int>(
                                "SELECT COUNT(PaperId) FROM Paper INNER JOIN Heading ON Paper.HeadingID = Heading.HeadingID INNER JOIN Meeting ON Heading.MeetingId = Meeting.MeetingId WHERE DocType != 3 AND NotificationStatus IN (2) AND RefId = " +
                                item.MeetingId +
                                " AND MeetingStatus IN (1,4) AND PaperStatus NOT IN (0,4,6) AND Heading.IsAllowed = 1 AND Paper.IsAllowed = 1");

                    if (ammendedPapers > 0)
                        item.PaperNotificationStatus = (int) PapernotificationStatus.Amended;
                }
                return lastMeetings;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<MeetingModel> GetMeetingById(int meetingId)
        {
            try
            {
                var conn = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                var meetingEntity =
                    await conn.Table<DB.Meeting>().Where(m => m.MeetingId.Equals(meetingId)).FirstOrDefaultAsync();
                var meetingModel = EntityToModelMapper.MapMeetingEntityToMeetingModel(meetingEntity);

                return meetingModel;
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<List<DateTime>> GetMeetingsByMonth(MonthSelection monthSelection)
        {
            try
            {
                var conn = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);

                var startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
                var endDate = startDate.AddMonths(1).AddDays(-1);

                var sqlCommand = new SQLiteCommand(conn)
                {
                    CommandText = "SELECT * FROM meeting WHERE meeting.date >= '" + startDate +
                                  "' and meeting.date <= '" + endDate + "'" +
                                  "and (meeting.meetingstatus == '" + (byte) MeetingStatus.Last +
                                  "' or meeting.meetingstatus == '" + (byte) MeetingStatus.Next +
                                  "' or meeting.meetingstatus == '" + (byte) MeetingStatus.Schedule + "')"
                };

                var meetingEntities = new List<DB.Meeting>();
                Task meetingListTask =
                    Task.Factory.StartNew(() => meetingEntities = sqlCommand.ExecuteQuery<DB.Meeting>().ToList());
                await meetingListTask;

                return meetingEntities.Select(x => x.Date).Distinct().ToList();
            }
            catch (Exception)
            {
                throw;
            }
        }

        public async Task<bool> AddMeetingNote(int memberId, int meetingId, string meetingNote)
        {
            try
            {
                var connAsync = _dbOperations.GetAsyncConnection(DbConnectionType.UserDbConnetion);

                await connAsync.RunInTransactionAsync((connection) =>
                {
                    connection = _dbOperations.GetSyncConnection(DbConnectionType.UserDbConnetion);

                    try
                    {
                        var logEntity = new Log
                        {
                            MemberId = Global.UserId,
                            MeetingID = meetingId,
                            Action = ApplicationConstants.LogActions.UpdateMeetingNote,
                            Data = meetingNote,
                            Date = DateTime.Now,
                            UpdateID = Guid.NewGuid().ToString()
                        }; //Create log table record
                        //TODO: set relevant value
                        //logEntity.Info = ""; //TODO: set relevant value
                        //logEntity.Status = ""; //TODO: set relevant value
                        //logEntity.LastUpdatedDateTime = null;//TODO: set relevant value
                        //logEntity.LastUpdatedTimeStamp = null;//TODO: set relevant value
                        //logEntity.UpdateID = "1";//TODO : set relevant value
                        connection.Insert(logEntity);

                        var meetingEntity = (connection.Table<DB.Meeting>().FirstOrDefault(m => m.MeetingId.Equals(meetingId)));

                        meetingEntity.MeetingNote =
                            meetingNote.Replace(ApplicationConstants.MeetingNoteNewLineChar.ToString(), "<br/>");
                        meetingEntity.LastUpdatedDateTime = DateTime.Now;

                        connection.Update(meetingEntity);
                        connection.Commit();
                    }
                    catch (Exception)
                    {
                        connection.Rollback();
                        throw;
                    }
                });
                return true;
            }
            catch (Exception)
            {
                throw;
            }
        }
    }
}